

import java.io.IOException;
import java.io.PrintWriter;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;

/**
 * Servlet implementation class Art_move
 */
public class Art_move extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public Art_move() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		response.setContentType("text/html");
		response.setCharacterEncoding("UTF-8");
		request.setCharacterEncoding("UTF-8");
		HttpSession Session=request.getSession();
		PrintWriter out = response.getWriter();
		if (request.getParameter("Begin")==null){
			out.println("<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">");
			out.println("<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\"><title>Система On-line отчетов</title></head><body>");
			out.println("<form method=\"get\">Начало периода:<input name=\"Begin\"><br>Конец периода:<input name=\"End\"><br>Наименование товара:<input name=\"name\"><br><input type=\"submit\" name=\"BOk\" value=\"     Ok!      \"></form></body></html>");
		}
		else{
			setBegin(request.getParameter("Begin"), Session);
			setEnd(request.getParameter("End"), Session);
			setName(request.getParameter("name"), Session);
			String SQL=String.format("select trim(td.name), d.numb, to_char(d.day,'DD.MM.YYYY'), d.sum, trim(c.name), l.kol, td.operacia, d.id_doc, c.id_client from TYPE_DOC td, DOCUMENT d, LINES l, CLIENT c where (d.day between '%s' and '%s')"+ 
					"and d.ID_DOC=l.ID_DOC and l.ID_TOVAR=%s and d.id_type_doc=td.id_type_doc and d.id_client=c.id_client and td.operacia between 1 and 2 order by d.day", getBegin(Session), getEnd(Session), getName(Session));
			ResultSet rs = null;
			Connection cn=null;
			Statement st=null;
			try {
				InitialContext initCtx = new InitialContext();
				DataSource ds=(DataSource)initCtx.lookup("java:comp/env/jdbc/newDs");
				cn=ds.getConnection();
				st=cn.createStatement();
				rs=st.executeQuery(SQL);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			out.println("<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">");
			out.println("<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\"><title>Система On-line отчетов</title></head><body>");
			out.println(String.format("<p align=\"center\">Отчет по движению %s <br> за период с %s по %s </p>",GetNamefromId(getName(Session)),getBegin(Session),getEnd(Session)) );
			out.println("<table border=\"1\" align=\"center\" cellpadding=\"5\"><thead><tr align=\"center\"><th>Контрагент</th><th>Документ</th><th>Приход <br> кол-во </th><th>Расход<br> кол-во</th></tr></thead>");
			try {
				while (rs.next())
					out.println(String.format("<tr><td><a href=\"%s\">%s</td><td>%s</td><td align=\"center\">%s</td><td align=\"center\">%s</td></tr>", 
							"client_zvit?Begin="+getBegin(Session)+"&End="+getEnd(Session)+"&name="+rs.getString(9),rs.getString(5), 
							(rs.getString(1)+" №"+rs.getString(2)+" от "+rs.getString(3)+" на сумму "+rs.getString(4)), (rs.getInt(7)==1?rs.getString(6):"-"), (rs.getInt(7)==2?rs.getString(6):"-")));
			}catch(SQLException e){
				e.printStackTrace();
			}
			out.println("</table></body></html>");
			try {
				rs.close();
				st.close();
				cn.close();
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	private void setBegin(String Value, HttpSession session){
		if(session!=null){
			session. setAttribute("begin", Value);
		}
	}
	private void setEnd(String Value, HttpSession session){
		if(session!=null){
			session. setAttribute("end", Value);
		}
	}
	private void setName(String Value, HttpSession session){
		if(session!=null){
			session. setAttribute("name", Value);
		}
	}
	private String getBegin(HttpSession session){
		if (session!=null)
			return (String)session.getAttribute("begin");
		else
			return "";
	}
	private String getEnd(HttpSession session){
		if (session!=null)
			return (String)session.getAttribute("end");
		else
			return "";
	}
	private String getName(HttpSession session){
		if (session!=null)
			return (String)session.getAttribute("name");
		else
			return "";
	}
	private String GetNamefromId(String id){
		ResultSet rs=null;
		String ret="";
		try {
			InitialContext initCtx = new InitialContext();
			DataSource ds=(DataSource)initCtx.lookup("java:comp/env/jdbc/newDs");
			Connection cn=ds.getConnection();
			Statement st=cn.createStatement();
			rs=st.executeQuery(String.format("select trim(name) from tovar where id_tovar=%s",id));
			if (rs.next()){
				ret=rs.getString(1);
			}
			rs.close();
			st.close();
			cn.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}
}
